﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace INSTA.BankApplication.DAL
{
    public abstract class AbstractDbAccessor
    {
        
        private IDbConnection       _connection;
        private IDbCommand          _command;
        private IDbTransaction      _transaction;
        private IDbDataAdapter      _adapter;
        


        /// <summary>
        /// Initializes a new instance of the <see cref="AbstractDbAccessor"/> class.
        /// </summary>
        /// <param name="mapper">The mapper.</param>
        /// <param name="connectionString">The connection string.</param>
        protected AbstractDbAccessor(DataMapper mapper, string connectionString)
        {
            Mapper              = mapper;
            ConnectionString    = connectionString;


            // Initializes the typed (SqlServer / MySQL) database objects.
            InitTypedDatabaseObjects();

        }


        /**********************************************************************
         * 
         * Methods
         * 
         **********************************************************************/


        /// <summary>
        /// Initializes the typed database objects.
        /// </summary>
        protected abstract void InitTypedDatabaseObjects();

        
        /// <summary>
        /// Gets a new connection.
        /// </summary>
        /// <returns>a new connection.</returns>
        protected abstract IDbConnection GetNewConnection();

        /// <summary>
        /// Gets a new Command.
        /// </summary>
        /// <returns>a new Command.</returns>
        protected abstract IDbCommand GetNewCommand();


        /// <summary>
        /// Gets the new transaction on the current connection.
        /// </summary>
        /// <returns>IDbTransaction.</returns>
        private IDbTransaction GetNewTransaction()
        {
            // sets the connection string.
            Connection.ConnectionString = ConnectionString;

            // Opens the connection.
            Connection.Open();

            // Begins the transaction..
            IDbTransaction transaction = Connection.BeginTransaction();

            // and return it.
            return transaction;

            
        }

        /**********************************************************************
         * 
         * Méthodes CRUD
         * 
         **********************************************************************/

        /// <summary>
        /// Returns a single-record DataTable 
        /// containing the record for the current
        /// instance's primary key value.
        /// </summary>
        /// <returns>A single-record DataTable</returns>        
        public virtual DataTable Select()
        {
            // Open the connection.
            Connection.ConnectionString = ConnectionString;
            Connection.Open();

            // Create a SqlCommand to retrieve table data.
            Command.CommandText = SqlGenerator.GenerateSQLFor_Select();
            Command.Connection  = Connection;
            Command.CommandType = CommandType.Text;

            // Set the SqlDataAdapter's SelectCommand.
            Adapter.SelectCommand = Command;

            // Fill the DataSet.
            DataSet dataSet = new DataSet("ResultDataset");
            Adapter.Fill(dataSet);

            Connection.Close();

            return dataSet.Tables[0];
        }


        /// <summary>
        /// Deletes the record for the specified Id
        /// and returns true if the number of affected
        /// records is 1, else false.
        /// </summary>
        /// <returns>true on success.</returns>
        public bool Delete()
        {
            
            string sqlCommandText = SqlGenerator.GenerateSQLFor_Delete();

            IDbTransaction transaction = GetNewTransaction();

            bool result = ExecuteCommandNonQuery(sqlCommandText,
                                                 transaction,
                                                 commitAndClose : true);
            return result;
        }


        /// <summary>
        /// Updates the current  instance in thge database
        /// </summary>
        /// <returns>true on success</returns>
        public bool Update()
        {
            
            string sqlCommand = SqlGenerator.GenerateSQLFor_Update();

            IDbTransaction transaction = GetNewTransaction();

            bool result = ExecuteCommandNonQuery(sqlCommand,
                                            transaction,
                                            commitAndClose : true);

            return result;
        }


        /// <summary>
        /// Inserts this instance in the database
        /// </summary>
        /// <returns>
        /// the last generated primary key
        /// value</returns>        
        public int Insert()
        {
            // Set the last generated Id to an indentifiable value.
            int lastGeneratedID = -1;

            // Gets a Transaction on an open connection, and begins the 
            // transaction.
            IDbTransaction transaction = GetNewTransaction();

            // Executes the INSERT statement.
            bool result = false;
            string sqlCommandText = SqlGenerator.GenerateSQLFor_Insert();
            result = ExecuteCommandNonQuery(sqlCommandText,
                                            transaction,
                                            commitAndClose: false);

            // Si l'INSERT a bien fonctionné (i.e. le nombre de records affectés == 1).
            if (result)
            {
                // Generates the command to get
                // back the last generated primary 
                // key value..
                IDbCommand command = GetNewCommand();
                command.CommandType = CommandType.Text;
                command.Connection = Connection;
                command.CommandText = SqlGenerator.GenerateSQLFor_LastGeneratedId();
                command.Transaction = transaction;

                // Executes the command and get back
                // the last generated Id..
                lastGeneratedID = int.Parse(command.ExecuteScalar().ToString());

                // On valide l'INSERT.
                transaction.Commit();

                // et l'on ferme la transaction.
                Connection.Close();
            }

            // et l'on renvoie comme valeur de retour de la méthode
            // la valeur du dernier Id généré pour cette table.
            return lastGeneratedID;
        }




        /// <summary>
        /// Executes the command non query.
        /// </summary>
        /// <param name="commandText">The command text.</param>
        /// <param name="transaction">The transaction.</param>
        /// <param name="commitAndClose">The commit and close.</param>
        /// <returns>Returns <c>true</c></returns>
        protected bool ExecuteCommandNonQuery(string commandText,
                                              IDbTransaction transaction,
                                              bool commitAndClose)
        {
            int recordsAffected = 0;

            try
            {

                // Command..
                Command.CommandType = CommandType.Text;
                Command.Connection  = transaction.Connection;
                Command.CommandText = commandText;
                Command.Transaction = transaction;


                // On exécute notre Command en mode
                // ExecuteNonQuery, et l'on récupère 
                // le nb de records affectés, qui doit 
                // tjs être 1.
                //Console.WriteLine("Executing : {0}", commandText );

                recordsAffected = Command.ExecuteNonQuery();

                // On Commit la transaction si besoin..
                if (commitAndClose)
                {
                    transaction.Commit();
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                // on fait un RollBack de notre transaction.
                transaction.Rollback();
            }
            finally
            {
                if (Connection.State == ConnectionState.Open
                    && commitAndClose)
                {
                    Connection.Close();
                }
            }

            return (recordsAffected == 1);

        }

        




        /**********************************************************************
         * 
         * Properties
         * 
         **********************************************************************/


        /// <summary>
        /// Gets or sets the connection string.
        /// </summary>
        /// <value>The connection string.</value>
        public string ConnectionString              { get; private set; }

        /// <summary>
        /// Gets the mapper.
        /// </summary>
        /// <value>The mapper.</value>
        public DataMapper Mapper                    { get; private set; }

        /// <summary>
        /// Gets the SQL generator.
        /// </summary>
        /// <value>The SQL generator.</value>
        public AbstractSqlGenerator SqlGenerator    { get;  set;        }


        /// <summary>
        /// Gets or sets the connection.
        /// </summary>
        /// <value>The connection.</value>
        public IDbConnection Connection
        {
            get { return _connection; }
            set { _connection = value; }
        }


        /// <summary>
        /// Gets or sets the command.
        /// </summary>
        /// <value>The command.</value>
        public IDbCommand Command
        {
            get { return _command; }
            set { _command = value; }
        }

        /// <summary>
        /// Gets or sets the adapter.
        /// </summary>
        /// <value>The adapter.</value>
        public IDbDataAdapter Adapter
        {
            get { return _adapter; }
            set { _adapter = value; }
        }
    }
}
